package com.example.ticketorder.repository;

import com.example.ticketorder.entity.Ticket;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import javax.transaction.Transactional;
import java.sql.Date;
import java.sql.Time;

public interface TicketRepository extends CrudRepository<Ticket,String> {
    @Query(nativeQuery = true,value = "SELECT DISTINCT destination FROM Ticket ")
    public Iterable<String> findDestination();
    //查询单个地址
    @Query(nativeQuery = true,value = "SELECT destination FROM Ticket WHERE car = ? and departure = ?")
    public String findDest(String car,String departure);
    @Query(nativeQuery = true,value = "SELECT DISTINCT car FROM Ticket ")
    public Iterable<String> findCar();
    @Query(nativeQuery = true,value = "SELECT DISTINCT Dtime FROM Ticket ")
    public Iterable<String> findDTime();
    @Query(nativeQuery = true,value = "SELECT min(poll) FROM Ticket WHERE car= ?")
    public int findPoll(String car);
    @Query(nativeQuery = true,value = "SELECT poll FROM Ticket WHERE car= ? and departure = ?")
    public int findZpoll(String car,String departure);
    @Query(nativeQuery = true,value = "SELECT money FROM Ticket WHERE car= ? and destination = ?")
    public double findMoney(String car,String destination);
    @Query(nativeQuery = true,value = "SELECT car FROM Ticket WHERE destination = ? and Dtime = ?")
    public Iterable<String> findScar(String destination, Date Dtime);
    @Query(nativeQuery = true,value = "SELECT Htimea FROM Ticket WHERE car = ? and departure = ?")
    public Time findHtimea(String car,String departure);
    @Query(nativeQuery = true,value = "SELECT Htimeb FROM Ticket WHERE car = ? and destination = ?")
    public Time findHtimeb(String car,String destination);
    @Query(nativeQuery = true,value = "SELECT Htimeb FROM Ticket WHERE destination = ? and car != ?")
    public Time findZHtimeb(String destination,String car);
    @Query(nativeQuery = true,value = "SELECT  departure FROM Ticket WHERE car = ? and departure !=?")
    public Iterable<String> findSite(String car,String departure);
    @Query(nativeQuery = true,value = "SELECT  departure FROM Ticket WHERE car = ? and Htimeb <= ?")
    public Iterable<String> findDeparture(String car,Time Htimeb);
    @Query(nativeQuery = true,value = "SELECT car FROM Ticket WHERE destination = ? and Htimeb < ? and car != ?")
    public Iterable<String> findZcar(String departure,Time Htimea,String car);

    //以下是更改数据
    //UPDATE 表名 SET 列1=新值1,列2=新值2 WHERE 过滤条件
    @Modifying
    @Transactional
    @Query(nativeQuery = true,value = "UPDATE Ticket SET poll=poll-1 WHERE car = ? and departure = ?")
    public void updatePoll(String car,String departure);
    @Modifying
    @Transactional
    @Query(nativeQuery = true,value = "UPDATE Ticket SET poll=poll+1 WHERE car = ? and departure = ?")
    public void updatePollq(String car,String departure);
}
